#include "UserTable.h"
#include "Ext/FilterQuery.h"

#include "Utility/Convertor.h"
#include "Utility/SqlFilter.h"
#include "DatabaseException.h"

using namespace TCServer::Utility;
using namespace std;

namespace TCServer
{
    namespace Store
    {

        void UserTable::create()
        {
            FilterQuery q(R"(

            CREATE TABLE `users` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `username` varchar(60) DEFAULT NULL,
              `email` varchar(60) DEFAULT NULL,
              `phone` varchar(30) DEFAULT NULL,
              `passwordHash` char(32) DEFAULT NULL,
              `createTime` datetime DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`id`),
              UNIQUE KEY `id_UNIQUE` (`id`),
              KEY `index_username` (`username`) USING HASH,
              KEY `index_email` (`email`) USING HASH,
              KEY `index_phone` (`phone`) USING HASH
            ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

			)");
            return q.exec();
        }

        quint64 UserTable::insert(const Type::User &user)
        {
            FilterQuery q;
            q.prepare(R"(

            INSERT INTO `tc`.`users`
                (
                `username`,
                `email`,
                `phone`,
                `passwordHash`
                )
                VALUES
                (
                ?,
                ?,
                ?,
                ?
                );

			)");
            q.addBindValue(user.username);
            q.addBindValue(user.email);
            q.addBindValue(user.phone);
            q.addBindValue(user.passwordHash);
            q.exec();
            return q.lastInsertId().toULongLong();
        }

        void UserTable::update(const Type::User &user)
        {
            FilterQuery q;
            q.prepare(R"(

            UPDATE `tc`.`users`
            SET
                `username` = ?,
                `email` = ?,
                `phone` = ?,
                `passwordHash` = ?
            WHERE
                `id` = ?;

			)");
            q.addBindValue(user.username);
            q.addBindValue(user.email);
            q.addBindValue(user.phone);
            q.addBindValue(user.passwordHash);
            q.addBindValue(user.id);
            q.exec();
        }

        void UserTable::remove(const quint64 id)
        {
            FilterQuery q;
            q.prepare(" DELETE FROM `tc`.`users` WHERE `id`=?; ");
            q.addBindValue(id);
            q.exec();
        }

        Type::User UserTable::query(const quint64 id)
        {
            FilterQuery q;
            q.prepare(R"(

			SELECT `users`.`id`,
				`users`.`username`,
				`users`.`email`,
				`users`.`phone`,
				`users`.`passwordHash`,
				`users`.`createTime`
			FROM `tc`.`users`
			WHERE `id` = ?;

			)");
            q.addBindValue(id);
            q.exec();

            if (!q.next())
                throw DatabaseLogicException(q);

            return Type::User{
                q.nextValue().toULongLong(),  // `users`.`id`,

                // login information
                q.nextValue().toString(), // `users`.`username`,
                q.nextValue().toString(), // `users`.`email`,
                q.nextValue().toString(), // `users`.`phone`,
                q.nextValue().toByteArray(), // `users`.`passwordHash`,
                q.nextValue().toDateTime(), // `users`.`createTime`,`
            };
        }

        std::tuple<bool, quint64> UserTable::id(const QString & username_email_phone, const QString & passwordHash)
        {
            FilterQuery q;
            q.prepare("SELECT id FROM tc.users"
                " WHERE (username = ? or email = ? or phone = ?) and passwordHash = ?;");
            q.addBindValue(username_email_phone);
            q.addBindValue(username_email_phone);
            q.addBindValue(username_email_phone);
            q.addBindValue(passwordHash);
            q.exec();
            if (q.next())
                return make_tuple(true, q.value(0).toULongLong());
            return make_tuple(false, 0);
        }

        void UserTable::flushLoginDatetime(const quint64 id)
        {

            FilterQuery q;
            q.prepare(R"(
                UPDATE `tc`.`users` SET `lastLogin` = CURRENT_TIMESTAMP
                WHERE `id` = ?;
)");
            q.addBindValue(id);
            q.exec();
            return;
        }

        bool UserTable::isUsernameExist(const QString &username)
        {
            FilterQuery q;
            q.prepare("SELECT count(*) FROM tc.users where username = ?;");
            q.addBindValue(username);
            q.exec();
            q.next();
            return q.value(0).toInt() > 0;
        }

        bool UserTable::isEmailExist(const QString & email)
        {
            FilterQuery q;
            q.prepare("SELECT count(*) FROM tc.users where email = ?;");
            q.addBindValue(SqlFilter::filterEmail(email));
            q.exec();
            q.next();
            return q.value(0).toInt() > 0;
        }

        bool UserTable::isPhoneExist(const QString & phone)
        {
            FilterQuery q;
            q.prepare("SELECT count(*) FROM tc.users where phone = ?;");
            q.addBindValue(SqlFilter::filterPhone(phone));
            q.exec();
            q.next();
            return q.value(0).toInt() > 0;
        }

        bool UserTable::isIdExist(quint64 userId)
        {
            FilterQuery q;
            q.prepare("SELECT count(*) FROM tc.users where id = ?;");
            q.addBindValue(userId);
            q.exec();
            q.next();
            return q.value(0).toInt() > 0;
        }

    }
}
